﻿namespace AnyOffice.DAL
{
    using AnyOffice.Model;
    using Microsoft.ApplicationBlocks.Data;
    using System;
    using System.Data;
    using System.Data.SqlClient;
    using System.Text;

    public class NetMeetingMessagesDao
    {
        public DataTable GetHistory(SqlConnection conn, int sessionId)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SELECT ");
            builder.Append("\t* ");
            builder.Append("FROM ");
            builder.Append("\t[dbo].[NetMeeting_Messages] ");
            builder.Append("WHERE ");
            builder.Append("\t[SessionId] = @SessionId ");
            builder.Append("\tAND [IsPrivate] = 0 ");
            builder.Append("\tAND [Content] NOT LIKE '%@CLOSE%' ");
            SqlParameter[] commandParameters = new SqlParameter[] { new SqlParameter("@SessionId", SqlDbType.Int) };
            commandParameters[0].Value = sessionId;
            DataSet set = SqlHelper.ExecuteDataset(conn, CommandType.Text, builder.ToString(), commandParameters);
            set.Tables[0].TableName = "NetMeeting_Messages";
            return set.Tables[0];
        }

        public DataTable GetMessage(SqlConnection conn, int sessionId, string userId)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("UPDATE [dbo].[NetMeeting_User] ");
            builder.Append("SET ");
            builder.Append("\t[State]=1, ");
            builder.Append("\t[LastRefreshTime] = GETDATE() ");
            builder.Append("WHERE ");
            builder.Append("\t[SessionId] = @SessionId ");
            builder.Append("\tAND [UserId] = @UserId ");
            builder.Append("DECLARE @LastMessageId int ");
            builder.Append("DECLARE @MaxMessageId int ");
            builder.Append("SELECT ");
            builder.Append("\t@LastMessageId = [LastMessageId] ");
            builder.Append("FROM ");
            builder.Append("\t[dbo].[NetMeeting_User] ");
            builder.Append("WHERE ");
            builder.Append("\t([UserId] = @UserId) AND ([SessionId] = @SessionId) ");
            builder.Append("SELECT ");
            builder.Append("\t@MaxMessageId = MAX([MessageId]) ");
            builder.Append("FROM ");
            builder.Append("\t[dbo].[NetMeeting_Messages] ");
            builder.Append("WHERE ");
            builder.Append("\t([MessageId] > @LastMessageId) ");
            builder.Append("\tAND ([SessionId] = @SessionId) ");
            builder.Append("\tAND ");
            builder.Append("\t( ");
            builder.Append("\t\t([UserId] = @UserId)  ");
            builder.Append("\t\tOR ((ISNULL([ToId],'')='') ");
            builder.Append("\t\tOR (ISNULL([ToId],'') = @UserId) ");
            builder.Append("\t \tOR ((ISNULL([ToId],'') != @UserId) AND ([IsPrivate] = 0)))  ");
            builder.Append("\t) ");
            builder.Append("\t ");
            builder.Append("IF @MaxMessageId IS NOT NULL ");
            builder.Append("\tUPDATE [dbo].[NetMeeting_User] ");
            builder.Append("\tSET ");
            builder.Append("\t\t[LastMessageId] = @MaxMessageId ");
            builder.Append("\tWHERE ");
            builder.Append("\t\t([UserId] = @UserId) ");
            builder.Append("\t\tAND ([SessionId] = @SessionId) ");
            builder.Append("\t ");
            builder.Append("SELECT ");
            builder.Append("\t[UserId], ");
            builder.Append("\t[NickName], ");
            builder.Append("\t[ToId], ");
            builder.Append("\t[ToNickName], ");
            builder.Append("\t[Color], ");
            builder.Append("\t[IsPrivate], ");
            builder.Append("\t[Content], ");
            builder.Append("\tCONVERT (nvarchar, [SendTime] ,20) AS SendTime ");
            builder.Append("FROM ");
            builder.Append("\t[dbo].[NetMeeting_Messages] ");
            builder.Append("WHERE ");
            builder.Append("\t([MessageId] > @LastMessageId) ");
            builder.Append("\tAND ([MessageId] <= @MaxMessageId) ");
            builder.Append("\tAND ([SessionId] = @SessionId) ");
            builder.Append("\tAND ");
            builder.Append("\t( ");
            builder.Append("\t\t([UserId] = @UserId)  ");
            builder.Append("\t\tOR ((ISNULL([ToId],'')='')  ");
            builder.Append("\t\tOR (ISNULL([ToId],'') = @UserId)  ");
            builder.Append("\t \tOR ((ISNULL([ToId],'') != @UserId) AND ([IsPrivate] = 0))) ");
            builder.Append("\t\tOR (ISNULL([UserId],'') ='')  ");
            builder.Append("\t) ");
            SqlParameter[] commandParameters = new SqlParameter[2];
            commandParameters[0] = new SqlParameter("@SessionId", SqlDbType.Int);
            commandParameters[0].Value = sessionId;
            commandParameters[1] = new SqlParameter("@UserId", SqlDbType.NVarChar, 20);
            commandParameters[1].Value = userId;
            DataSet set = SqlHelper.ExecuteDataset(conn, CommandType.Text, builder.ToString(), commandParameters);
            set.Tables[0].TableName = "NetMeeting_Messages";
            return set.Tables[0];
        }

        public bool Insert(SqlConnection conn, NetMeetingMessagesData data)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("IF ISNULL(@ToId,'')='' ");
            builder.Append("\tINSERT [dbo].[NetMeeting_Messages] ");
            builder.Append("\t( ");
            builder.Append("\t\t[SessionId], ");
            builder.Append("\t\t[UserId], ");
            builder.Append("\t\t[NickName],\t\t ");
            builder.Append("\t\t[Color], ");
            builder.Append("\t\t[IsPrivate], ");
            builder.Append("\t\t[Content], ");
            builder.Append("\t\t[SendTime] ");
            builder.Append("\t) ");
            builder.Append("\tSELECT  ");
            builder.Append("\t\t@SessionId, ");
            builder.Append("\t\t@UserId, ");
            builder.Append("\t\t[NickName], ");
            builder.Append("\t\t@Color, ");
            builder.Append("\t\t@IsPrivate, ");
            builder.Append("\t\t@Content, ");
            builder.Append("\t\tGETDATE() ");
            builder.Append("\tFROM ");
            builder.Append("\t\t[dbo].[NetMeeting_User] ");
            builder.Append("\tWHERE ");
            builder.Append("\t\t[UserId] = @UserId ");
            builder.Append("\t\tAND [SessionId] = @SessionId ");
            builder.Append("ELSE ");
            builder.Append("\tINSERT [dbo].[NetMeeting_Messages] ");
            builder.Append("\t( ");
            builder.Append("\t\t[SessionId], ");
            builder.Append("\t\t[UserId], ");
            builder.Append("\t\t[NickName], ");
            builder.Append("\t\t[ToId], ");
            builder.Append("\t\t[ToNickName], ");
            builder.Append("\t\t[Color], ");
            builder.Append("\t\t[IsPrivate], ");
            builder.Append("\t\t[Content], ");
            builder.Append("\t\t[SendTime] ");
            builder.Append("\t) ");
            builder.Append("\tSELECT  ");
            builder.Append("\t\t@SessionId, ");
            builder.Append("\t\t@UserId, ");
            builder.Append("\t\ta.NickName, ");
            builder.Append("\t\t@ToId, ");
            builder.Append("\t\tb.NickName, ");
            builder.Append("\t\t@Color, ");
            builder.Append("\t\t@IsPrivate, ");
            builder.Append("\t\t@Content, ");
            builder.Append("\t\tGETDATE() ");
            builder.Append("\tFROM ");
            builder.Append("\t\t[dbo].[NetMeeting_User] a, ");
            builder.Append("\t\t[dbo].[NetMeeting_User] b ");
            builder.Append("\tWHERE ");
            builder.Append("\t\ta.UserId = @UserId ");
            builder.Append("\t\tAND b.UserId = @ToId ");
            builder.Append("\t\tAND a.SessionId = @SessionId ");
            builder.Append("\t\tAND b.SessionId = @SessionId ");
            SqlParameter[] commandParameters = new SqlParameter[6];
            commandParameters[0] = new SqlParameter("@SessionId", SqlDbType.Int);
            commandParameters[0].Value = data.SessionId;
            commandParameters[1] = new SqlParameter("@UserId", SqlDbType.NVarChar, 20);
            commandParameters[1].Value = data.UserId;
            commandParameters[2] = new SqlParameter("@ToId", SqlDbType.NVarChar, 20);
            commandParameters[2].Value = data.ToId;
            commandParameters[3] = new SqlParameter("@IsPrivate", SqlDbType.Bit);
            commandParameters[3].Value = data.IsPrivate;
            commandParameters[4] = new SqlParameter("@Content", SqlDbType.NText);
            commandParameters[4].Value = data.Content;
            commandParameters[5] = new SqlParameter("@Color", SqlDbType.NVarChar, 10);
            commandParameters[5].Value = data.Color;
            SqlHelper.ExecuteNonQuery(conn, CommandType.Text, builder.ToString(), commandParameters);
            return true;
        }
    }
}

